package de.stalinski.accounting;

import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;
import de.stalinski.logging.FrbrLogger;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import javax.servlet.http.HttpServlet;

public class ConsumersConnector extends HttpServlet {

    private FrbrLogger logger = FrbrLogger.instance();
    private static String sUsr = "root";
    private static String sPwd = "admin";
    private static String sDB = "frbrUsers";
    private static String sTable = "customers";
    private static Connection connection;
    private static String sHost = "localhost";
    private static String sConn = "jdbc:mysql://" + sHost + ":3306/" + sDB;
    private ArrayList<String> customers = new ArrayList();
    private HashMap forename = new HashMap();
    private HashMap surname = new HashMap();
    private HashMap username = new HashMap();
    private HashMap ids = new HashMap();
    private long threeDays = 10 * 24 * 60 * 60 * 1000;

    public ConsumersConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        try {
            if (connection == null) {
                connection = DriverManager.getConnection(sConn, sUsr, sPwd);
            }

        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
    }

    public void updateLastOrder(String id) throws ClassNotFoundException, SQLException {
        Date date = new Date(System.currentTimeMillis());
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String dateString = df.format(date);
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        st.execute("UPDATE "+sTable+" SET LASTORDER='"+dateString+"' WHERE ID='"+id+"'");
    }

    public String getFavorites() throws ClassNotFoundException, SQLException {
        Date threeDaysPast = new Date(System.currentTimeMillis() - threeDays);
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String dateString = df.format(threeDaysPast);
        String out = "<table class=\"status\"><tbody><tr>";
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable + " WHERE LASTORDER>='"+dateString+"' ORDER BY Nachname ASC");
        int count = 1;
        while (rs.next()) {
            String fn = rs.getString("Vorname");
            String sn = rs.getString("Nachname");
            String un = rs.getString("Username");
            String id = rs.getString("ID");
            System.out.println(fn+" "+sn+" "+un);
            out += "<td><div class=\"favos\"><a href=\"drinks.jsp" +
                    "?fn="+fn+"&sn="+sn+"&un="+un+"&id="+id+"\">"+sn+", "+fn+"</a></div></td>";

            count++;
            if(count>=5) {
                out+="</tr><tr>";
                count=1;
            }
        }
        out+="</tr></tbody></table>";
        return out;
    }

    

    public String getConsumptionForUserAndSort(String fn, String sn, String sort) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection(sConn, sUsr, sPwd);


        String s = sort.replaceAll(" ", "");
        String result = "";
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable + " WHERE Vorname='" + fn + "' AND Nachname='" + sn + "'");
        while (rs.next()) {
            result += rs.getString(s);
        }
        connection.close();
        return result;
    }

    public ArrayList<String> getMailAddresses() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection(sConn, sUsr, sPwd);


        ArrayList<String> result = new ArrayList();
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable);
        while (rs.next()) {
            String fn = rs.getString("Vorname");
            String sn = rs.getString("Nachname");
            String mail = rs.getString("Mail");

            String putValue = fn + "|" + sn + "|" + mail;

            result.add(putValue);
        }
        connection.close();
        return result;
    }

    public HashMap<String, Double> getRecipients() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection(sConn, sUsr, sPwd);


        HashMap<String, Double> result = new HashMap();

        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable);
        while (rs.next()) {
            Double sum = rs.getDouble("Summe");
            String fn = rs.getString("Vorname");
            String sn = rs.getString("Nachname");
            String mail = rs.getString("Mail");
            String putValue = fn + "|" + sn + "|" + mail;
            if (sum >= 15.0) {
                result.put(putValue, sum);
            }
        }
        connection.close();
        return result;
    }

    public void pay(String fn, String sn, Double value) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable);
        try {
            st.execute("UPDATE " + sTable + " SET paid=paid" + value + " WHERE Vorname ='" + fn + "' AND Nachname='" + sn + "'");
        } catch (MySQLSyntaxErrorException ex) {
        }
        connection.close();
    }

    public void book(String fn, String sn, String sort, int amount) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable);
        String setSort = sort.replaceAll(" ", "");
        Double price = new PricesTableConnector().getPriceFor(sort);
        Double newSum = price * amount;
        try {
            st.execute("UPDATE " + sTable + " SET " + setSort + "=" + setSort + "+" + amount + " WHERE Vorname ='" + fn + "' AND Nachname='" + sn + "'");
            st.execute("UPDATE " + sTable + " SET Summe=Summe+" + newSum + " WHERE Vorname ='" + fn + "' AND Nachname='" + sn + "'");
            new PricesTableConnector().updateStock(sort, amount);
        } catch (MySQLSyntaxErrorException ex) {
        }
        connection.close();
    }

    public String getCustomers(String index, String status) throws UnsupportedEncodingException, SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        customers.clear();
        String out = "<table class=\"status\"><tbody><tr>";
        try {
            Statement st = null;
            ResultSet rs = null;
            st = connection.createStatement();
            rs = st.executeQuery("select * from " + sTable);
            int i = 0;
            while (rs.next()) {
                String foundIndex = rs.getString("Nachname").substring(0, 1);
                String foundStatus = rs.getString("Status");
                if (foundIndex.equals(index) && foundStatus.equals(status)) {
                    String value = rs.getString("Nachname") + ", " + rs.getString("Vorname");
                    customers.add(value);
                    forename.put(value, rs.getString("Vorname"));
                    surname.put(value, rs.getString("Nachname"));
                    username.put(value, rs.getString("Username"));
                    ids.put(value, rs.getString("ID"));
                }
            }
            Collections.sort(customers);
            if (customers.size() <= 0) {
                return null;
            }
            for (String s : customers) {
                String link = "drinks.jsp?fn=" + forename.get(s) + "&sn=" + surname.get(s)+"&un="+username.get(s)+"&id="+ids.get(s);
                if (i > 3) {
                    out += "<tr>";
                }
                out += "<td>" +
                        "<a href=\"" + link + "\">" +
                        s +
                        "</a>" +
                        "</td>";
                if (i > 3) {
                    i = 0;
                }
                i++;
            }
        } catch (SQLException ex) {
        }
        connection.close();
        return out += "</tr></tbody></table>";
    }
}